# -*-encoding=utf-8-*-
__author__ = '帆'

import MySQLdb, Queue, math

class Db:

    host = 'master.mysql.fdd'

    user = 'admin'

    dbName= 'fdd_second_house'

    passwd = 'Admin_12345'

    db = None

    cursor = None

    queue = None

    tagDict = {}

    def __init__(self, queue):
        self.queue = queue
        self.connect()

    def connect(self):
        try:
            self.db = MySQLdb.connect(host = self.host, user = self.user, passwd = self.passwd, db = self.dbName, port = 3306, charset = 'utf8')
            self.cursor = self.db.cursor()
            self.cursor.execute('SET NAMES UTF-8')
            # cur.execute('select * from user')
            # cur.close()
            # conn.close()
        except MySQLdb.Error,e:
            print(e.message)

    def getCell(self):
        sql = 'SELECT * FROM t_second_cell WHERE cell_tag_id IS NULL'
        count = self.execSql(sql)

        page = int(math.ceil(count / 200.0))
        p = 1
        for i in range(0, page):
            offset = (p-1)*200
            sql = 'SELECT cell_id,cell_lng,cell_lat FROM t_second_cell WHERE cell_tag_id IS NULL LIMIT {0},{1}'.format(offset, 200)
            self.execSql(sql)
            result = self.cursor.fetchall()
            for r in result:

                # print(type(self.queue))
                self.queue.put(r)
            p += 1
        #print(self.queue)

    def updateCellTag(self, cellId, tagId):
        sql = 'UPDATE t_second_cell SET cell_tag_id="{0}" WHERE cell_id={1}'.format(tagId, cellId)
        print(sql)
        result = self.execSql(sql)
        if result > 0:
            self.db.commit()
        # print(result)
        return result

    def getCellTags(self):
        if self.tagDict:
            return self.tagDict

        sql = 'SELECT tag_id,tag_name FROM t_second_cell_tags'
        self.execSql(sql)
        result = self.cursor.fetchall()
        for r in result:
            tip = r[1].encode('utf-8')
            self.tagDict[tip] = r[0]
        return self.tagDict

    def execSql(self, sql):
        try:
            result = self.cursor.execute(sql)
        except (AttributeError, MySQLdb.OperationalError), e:
            print(e.message)
            self.connect()
            result = self.cursor.execute(sql)
        return result

    def __del__(self):
        self.cursor.close()
        self.db.close()
